<?php
 include_once "../lib/DAL/SinhVienLop.php";

 class SinhVienLopBLL{
   
    function __construct(){
       
    }
    
    function __destruct(){
        
    }
    
    function setSVLop($row){
            $lpsv=new SinhVienLop();
            $lpsv->setSinhVienLop($row['ma_svlop'],$row['ma_nhaphoc'],$row['ma_lop'],$row['ngaybatdau']);
            return $lpsv;
        }
    function setSVLopBLL($row){
            $lpsv=new SinhVienLop();
            $lpsv->setSVLop($row['ma_nhaphoc'],$row['ma_lop'],$row['ngaybatdau']);
            return $lpsv;
        }
    public function getsv_arrlop($malop){        
        $sql="SELECT ma_sv FROM tbl_sv_lop,tbl_nhaphoc WHERE tbl_sv_lop.ma_nhaphoc=tbl_nhaphoc.ma_nhaphoc AND ma_lop IN ($malop)";
        $kq=mysql_query($sql);
          
            if(mysql_num_rows($kq)>0){
                $arr=null;
                while($row = mysql_fetch_array($kq)){
                    $arr[] = $row['ma_sv'];
                }
            }
        return $arr;
    }
    
     public function getmasv_lop(){        
        $sql="SELECT ma_svlop FROM tbl_sv_lop";
        $kq=mysql_query($sql);
          
            if(mysql_num_rows($kq)>0){
                $arr=null;
                while($row = mysql_fetch_array($kq)){
                    $arr[] = $row['ma_svlop'];
                }
            }
        return $arr;
    }
    
    
   public function getDonVi_Khoa_SV($madonvi,$namkhoahoc){
    
        $sql="  SELECT ma_nhaphoc,tbl_nhaphoc.ma_sv,hoten_sv, gioitinh_sv, ngaysinh_sv
                FROM tbl_nhaphoc, tbl_sinhvien, tbl_donvi, tbl_donvi_ctdt, tbl_ctdt, tbl_khoahoc
                WHERE tbl_nhaphoc.ma_sv = tbl_sinhvien.ma_sv
                    AND tbl_nhaphoc.ma_trungtam = tbl_donvi.ma_donvi = tbl_donvi_ctdt.ma_donvi
                    AND tbl_donvi_ctdt.ma_ctdt = tbl_ctdt.ma_ctdt
                    AND tbl_nhaphoc.id_khoahoc = tbl_khoahoc.id_khoahoc
                    AND tbl_khoahoc.id_dv_ctdt = tbl_donvi_ctdt.id_dv_ctdt
                    AND nam_khoahoc='$namkhoahoc'
                    AND tbl_nhaphoc.ma_trungtam='$madonvi'
                    AND ma_nhaphoc NOT IN (SELECT ma_nhaphoc from tbl_sv_lop)
                LIMIT 0,20";
        $result = mysql_query($sql) or die(mysql_error());
        $arr= array();
        while($row = mysql_fetch_array($result)){
            $arr[] = $row;
        }
        return $arr;
    
   }
   
   public function getDonVi_Khoa_Nganh($madonvi,$makhoahoc){
    
        $sql="  Select a.madm_nganh,ten_nganh 
        FROM tbl_ctdt a,dm_nganh b, tbl_donvi_ctdt c, tbl_khoahoc d
        WHERE a.ma_ctdt = c.ma_ctdt
        AND   c.id_dv_ctdt=d.id_dv_ctdt
        AND   a.madm_nganh=b.madm_nganh
        AND   c.ma_donvi='$ma_donvi'
        AND   d.nam_khoahoc='$namkhoahoc'";
        $result = mysql_query($sql) or die(mysql_error());
        $arr= array();
        while($row = mysql_fetch_array($result)){
            $arr[] = $row;
        }
        return $arr;
    
   }
   
  public function countBanghi($namkhoahoc,$ma_donvi,$mang_nganh){
        $tongsobanghi=mysql_query("SELECT ma_nhaphoc,tbl_nhaphoc.ma_sv,hoten_sv, gioitinh_sv, madm_bh, namtotnghiep_sv, noitotnghiep_sv, noicongtac_sv
                FROM tbl_nhaphoc, tbl_sinhvien, tbl_donvi, tbl_donvi_ctdt, tbl_ctdt, tbl_khoahoc
                WHERE tbl_nhaphoc.ma_sv = tbl_sinhvien.ma_sv
                    AND tbl_nhaphoc.ma_trungtam = tbl_donvi.ma_donvi = tbl_donvi_ctdt.ma_donvi
                    AND tbl_donvi_ctdt.ma_ctdt = tbl_ctdt.ma_ctdt
                    AND tbl_nhaphoc.id_khoahoc = tbl_khoahoc.id_khoahoc
                    AND tbl_khoahoc.id_dv_ctdt = tbl_donvi_ctdt.id_dv_ctdt
                    AND   nam_khoahoc='$namkhoahoc'
                    AND   tbl_nhaphoc.ma_trungtam='$ma_donvi'
                    AND   ma_nhaphoc NOT IN (SELECT ma_nhaphoc from tbl_sv_lop)
                    AND   madm_nganh IN ('.$mang_nganh.')");
        
        return mysql_num_rows($tongsobanghi); 
    }
    
    public function getLop(){
    
    $sql="  SELECT DISTINCT ma_lop,ten_lop
                FROM tbl_lop
                WHERE  ma_lop IN ( SELECT ma_lop FROM tbl_sv_lop)";
        $result = mysql_query($sql) or die(mysql_error());
        $arr= array();
        while($row = mysql_fetch_array($result)){
            $arr[] = $row;
        }
        return $arr;
    
   }
   
    public function getsv_lopBYID($id){
            $sql="  SELECT * FROM tbl_sv_lop WHERE ma_svlop= '".$id."'";
            $result = mysql_query($sql) or die(mysql_error());
            $arrLop=array();
            while($row = mysql_fetch_array($result)){
                $arrLop[]=$this->setSVLop($row);
            }
            return $arrLop[0];
        }
    
    public function countLop(){
        $solop=mysql_query("SELECT DISTINCT ma_lop,ten_lop
                            FROM tbl_lop
                            WHERE  ma_lop IN ( SELECT ma_lop FROM tbl_sv_lop)");
        
        return mysql_num_rows($solop); 
    }
   
    public function insertLopSinhVien($lopsv){      
           $sql ="INSERT INTO tbl_sv_lop(
               ma_nhaphoc
              ,ma_lop
              ,ngaybatdau
            ) VALUES (
              '".$lopsv->getma_nhaphoc()."'  
              ,'".$lopsv->getma_lop()."' 
              ,'".$lopsv->getngaybatdau()."'  
            )" ;
            $result = mysql_query($sql);
            return $result;
       
    }
    
    public function updateLopSinhVien($lopsv){      
           $sql ="UPDATE tbl_sv_lop
                SET
                    ma_nhaphoc =  '".$lopsv->getma_nhaphoc()."'
                   ,ma_lop = '".$lopsv->getma_lop()."' 
                   ,ngaybatdau = '".$lopsv->getngaybatdau()."'
                WHERE ma_svlop = '".$lopsv->getma_svlop()."' ";
              
            $result = mysql_query($sql) or die(mysql_error());
            return $result;
       
    }
    
    public function deleteLopSVbyID($id){
            $sql="  DELETE FROM tbl_sv_lop WHERE ma_svlop = '$id'";
            $kq = mysql_query($sql) or die(mysql_error());
            return $kq;
   
   }
  }   
 ?>